package com.soft.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.soft.domain.Phone;
import com.soft.domain.User;

public class PhoneDao {
	private static PhoneDao dao=new PhoneDao();
	private static Connection con=null;
	private static Statement st=null;
	private static PreparedStatement ps=null;
	private static ResultSet rs=null;
	 
	public boolean add(Phone phone) throws SQLException{
		con=DB.connection(con);
		int num=dao.getPageCount(phone.getId())+1;
		phone.setPage((num/phone.PAGESIZE)+1);
		phone.setNumbers(num);
		String sql="insert into Phones(id,username,phonenumber,userid,page,numbers) values(?,?,?,?,?,?)";
		try {
			ps=(PreparedStatement) con.prepareStatement(sql);
			ps.setString(1, phone.getId());
			ps.setString(2, phone.getUsername());
			ps.setString(3, phone.getPhonenumber());
			ps.setString(4, phone.getUserid());
			ps.setInt(5, phone.getPage());
			ps.setInt(6,phone.getNumbers());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
			
	}

	public boolean add(String id,String username,String phonenumber,String userid) throws SQLException{
		con=DB.connection(con);
		int num=dao.getPageCount(id)+1;
		int page=(num/Phone.PAGESIZE)+1;
		String sql="insert into Phones(id,username,phonenumber,userid,page,numbers) values('"
																			+id+"','"
																			+username+"','"
																			+phonenumber+"','"
																			+userid+"',"
																			+page+","
																			+num+");";
		try {
			ps=(PreparedStatement) con.prepareStatement(sql);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
			
	}
	
	public int getPageCount(String id) throws SQLException {
		con=DB.connection(con);
		String sql="select * from Phones;";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		int i=0;
		while(rs.next()){
			if(id.equals(rs.getString("id"))){
				i++;	
			}
		}
		return i;
	}
	
	public boolean find(String id) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Phones;";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(id.equals(rs.getString("id"))){
				return true;
			}
		}
		return false;
	}
	
	public boolean find(String username,String phonenumber) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Phones;";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(username.equals(rs.getString("username")) && phonenumber.equals(rs.getString("phonenumber"))){
				return true;
			}
		}
		return false;
	}
	
	public boolean exitUsername(String username) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Phones;";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(username.equals(rs.getString("username"))){
				return true;
			}
		}
		return false;
	}

	public boolean exitUsername(String username,String id) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Phones;";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(username.equals(rs.getString("username")) && id.equals(rs.getString("id"))){
				return true;
			}
		}
		return false;
	}
	
	public boolean exitUserid(String userid) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Phones;";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(userid.equals(rs.getString("userid"))){
				return true;
			}
		}
		return false;
	}
	
	public List getPhonenumber(String username) throws SQLException{
		con=DB.connection(con);
		List list=new ArrayList();
		String sql="select * from Phones;";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(username.equals(rs.getString("username"))){
				list.add(rs.getString("phonenumber"));
			}
		}
		return list;
	}
	
	
	public boolean findPhonenumber(String phonenumber) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Phones;";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(phonenumber.equals(rs.getString("phonenumber"))){
				return true;
			}
		}
		return false;
	}
	
	public Phone getPhone(String userid) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Phones where userid='"+userid+"';";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(userid.equals(rs.getString("userid"))){
				Phone phone =new Phone();
				phone.setId(rs.getString("id"));
				phone.setUsername(rs.getString("username"));
				phone.setUserid(rs.getString("userid"));
				phone.setPhonenumber(rs.getString("phonenumber"));
				phone.setPage(rs.getInt("page"));
				phone.setNumbers(rs.getInt("numbers"));
				return phone;
			}
		}
		return null;
	}
	
	
	public int getPageNumber(String userid) throws SQLException{
		con=DB.connection(con);
		if(dao.exitUserid(userid)){
			String sql="select * from Phones where userid='"+userid+"';";
			st=(Statement) con.createStatement();
			ResultSet rs=(ResultSet) st.executeQuery(sql);
			while(rs.next()){
				int numbers=rs.getInt("numbers");
				return numbers;
			}
		}
		return 0;
	}
	
	public boolean delete(Phone phone) throws SQLException{
		con=DB.connection(con);
		if(dao.find(phone.getId())){
			int pagenumbers=dao.getPageNumber(phone.getUserid());
			String sql="delete from Phones where userid='"+phone.getUserid()+"';";	

			String sql1="select * from Phones where id='"+phone.getId()+"' and numbers >"+pagenumbers+" group by numbers;";
			ps=(PreparedStatement) con.prepareStatement(sql1);
			ps.executeUpdate(sql);
			rs=(ResultSet)ps.executeQuery();
			while(rs.next()){
				int numbers=rs.getInt("numbers");
				int page;
				if((numbers-1)%Phone.PAGESIZE==0){
					page=(numbers-1)/Phone.PAGESIZE;
				}else{
					page=((numbers-1)/Phone.PAGESIZE+1);
				}
				String sql2="update Phones set numbers="+(numbers-1)+" where numbers="+numbers;
				String sql3="update Phones set page="+page+" where numbers="+numbers;
				st.executeUpdate(sql3);
				st.executeUpdate(sql2);			
			}
			
			return true;
		}
		return false;		
	}
	
	public boolean update(Phone phone) throws SQLException{
		con=DB.connection(con);
		if(dao.find(phone.getId())){
			String sql1="update Phones set username='"+phone.getUsername()+"' where userid='"+phone.getUserid()+"';";
			String sql2="update Phones set phonenumber='"+phone.getPhonenumber()+"' where userid='"+phone.getUserid()+"';";
			st=(Statement) con.createStatement();
			st.executeUpdate(sql1);
			st.executeUpdate(sql2);
			return true;
		}
		return false;		
	}
	
	public boolean update(String id,String username,String phonenumber,String userid) throws SQLException{
		con=DB.connection(con);
		if(dao.find(id)){
			String sql1="update Phones set username='"+username+"' where userid='"+userid+"';";
			String sql2="update Phones set phonenumber='"+phonenumber+"' where userid='"+userid+"';";
			st=(Statement) con.createStatement();
			st.executeUpdate(sql1);
			st.executeUpdate(sql2);
			return true;
		}
		return false;
			
	}
	
	public Map<String,Phone> finAll(String id) throws SQLException{
		con=DB.connection(con);
		Map<String,Phone> map=new HashMap<String,Phone>();
		
		String sql="select * from Phones where id='"+id+"';";
		st=(Statement) con.createStatement();
		ResultSet rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
				Phone phone =new Phone();
				String username=rs.getString("username");
				String userid=rs.getString("userid");
				String phonenumber=rs.getString("phonenumber");
				int page=rs.getInt("page");
				int numbers=rs.getInt("numbers");
				phone.setId(id);
				phone.setPage(page);
				phone.setUsername(username);
				phone.setUserid(userid);
				phone.setPhonenumber(phonenumber);
				phone.setNumbers(numbers);
				map.put(phone.getUserid(),phone);
			
		}
		
		rs.close();
		st.close();
		return map;
		
		
	}
	
	public void finAll(User user){
		con=DB.connection(con);
	}
}
